package com.pethospital.dao.impl;

import com.pethospital.dao.AppointmentDao;
import com.pethospital.entity.Appointment;
import com.pethospital.entity.PageResult;
import com.pethospital.entity.Pet;
import com.pethospital.entity.User;
import com.pethospital.util.DBUtil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

/**
 * 预约数据访问实现类
 * Appointment Data Access Object Implementation
 * 
 * @author PetHospital System
 * @version 1.0
 */
public class AppointmentDaoImpl implements AppointmentDao {
    
    @Override
    public Appointment findById(Integer appointmentId) {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.appointment_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, appointmentId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToAppointment(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return null;
    }
    
    @Override
    public List<Appointment> findByOwnerId(Integer ownerId) {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.owner_id = ? ORDER BY a.appointment_date DESC, a.appointment_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return appointments;
    }
    
    @Override
    public List<Appointment> findByPetId(Integer petId) {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.pet_id = ? ORDER BY a.appointment_date DESC, a.appointment_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return appointments;
    }
    
    @Override
    public int insert(Appointment appointment) {
        String sql = "INSERT INTO appointments (pet_id, owner_id, appointment_date, appointment_time, " +
                    "service_type, symptoms, status, doctor_name, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, appointment.getPetId());
            pstmt.setInt(2, appointment.getOwnerId());
            pstmt.setDate(3, appointment.getAppointmentDate());
            pstmt.setTime(4, appointment.getAppointmentTime());
            pstmt.setString(5, appointment.getServiceType());
            pstmt.setString(6, appointment.getSymptoms());
            pstmt.setString(7, appointment.getStatus() != null ? appointment.getStatus() : "pending");
            pstmt.setString(8, appointment.getDoctorName());
            pstmt.setString(9, appointment.getNotes());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int update(Appointment appointment) {
        String sql = "UPDATE appointments SET pet_id = ?, appointment_date = ?, appointment_time = ?, " +
                    "service_type = ?, symptoms = ?, status = ?, doctor_name = ?, notes = ? " +
                    "WHERE appointment_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, appointment.getPetId());
            pstmt.setDate(2, appointment.getAppointmentDate());
            pstmt.setTime(3, appointment.getAppointmentTime());
            pstmt.setString(4, appointment.getServiceType());
            pstmt.setString(5, appointment.getSymptoms());
            pstmt.setString(6, appointment.getStatus());
            pstmt.setString(7, appointment.getDoctorName());
            pstmt.setString(8, appointment.getNotes());
            pstmt.setInt(9, appointment.getAppointmentId());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int delete(Integer appointmentId) {
        String sql = "DELETE FROM appointments WHERE appointment_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, appointmentId);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int batchDelete(Integer[] appointmentIds) {
        if (appointmentIds == null || appointmentIds.length == 0) {
            return 0;
        }
        
        StringBuilder sql = new StringBuilder("DELETE FROM appointments WHERE appointment_id IN (");
        for (int i = 0; i < appointmentIds.length; i++) {
            sql.append("?");
            if (i < appointmentIds.length - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < appointmentIds.length; i++) {
                pstmt.setInt(i + 1, appointmentIds[i]);
            }
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public List<Appointment> findAll() {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "ORDER BY a.appointment_date DESC, a.appointment_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return appointments;
    }
    
    @Override
    public PageResult<Appointment> findByPage(int currentPage, int pageSize) {
        return findByCondition(null, null, null, null, null, currentPage, pageSize);
    }
    
    @Override
    public PageResult<Appointment> findByOwnerIdAndPage(Integer ownerId, int currentPage, int pageSize) {
        return findByCondition(null, null, null, null, ownerId, currentPage, pageSize);
    }
    
    /**
     * 将ResultSet映射为Appointment对象
     * 
     * @param rs ResultSet对象
     * @return Appointment 预约对象
     * @throws SQLException SQL异常
     */
    private Appointment mapResultSetToAppointment(ResultSet rs) throws SQLException {
        Appointment appointment = new Appointment();
        appointment.setAppointmentId(rs.getInt("appointment_id"));
        appointment.setPetId(rs.getInt("pet_id"));
        appointment.setOwnerId(rs.getInt("owner_id"));
        appointment.setAppointmentDate(rs.getDate("appointment_date"));
        appointment.setAppointmentTime(rs.getTime("appointment_time"));
        appointment.setServiceType(rs.getString("service_type"));
        appointment.setSymptoms(rs.getString("symptoms"));
        appointment.setStatus(rs.getString("status"));
        appointment.setDoctorName(rs.getString("doctor_name"));
        appointment.setNotes(rs.getString("notes"));
        appointment.setCreateTime(rs.getTimestamp("create_time"));
        appointment.setUpdateTime(rs.getTimestamp("update_time"));
        
        // 设置宠物信息
        String petName = rs.getString("pet_name");
        if (petName != null) {
            Pet pet = new Pet();
            pet.setPetId(rs.getInt("pet_id"));
            pet.setPetName(petName);
            pet.setPetType(rs.getString("pet_type"));
            appointment.setPet(pet);
        }
        
        // 设置主人信息
        String ownerName = rs.getString("owner_name");
        if (ownerName != null) {
            User owner = new User();
            owner.setUserId(rs.getInt("owner_id"));
            owner.setRealName(ownerName);
            appointment.setOwner(owner);
        }
        
        return appointment;
    }

    @Override
    public PageResult<Appointment> findByCondition(String petName, String serviceType, String status,
                                                  Date appointmentDate, Integer ownerId, int currentPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                                            "FROM appointments a " +
                                            "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                                            "LEFT JOIN users u ON a.owner_id = u.user_id WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 构建查询条件
        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND p.pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }
        if (serviceType != null && !serviceType.trim().isEmpty()) {
            sql.append(" AND a.service_type = ?");
            params.add(serviceType);
        }
        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND a.status = ?");
            params.add(status);
        }
        if (appointmentDate != null) {
            sql.append(" AND a.appointment_date = ?");
            params.add(appointmentDate);
        }
        if (ownerId != null) {
            sql.append(" AND a.owner_id = ?");
            params.add(ownerId);
        }

        // 先查询总数
        long totalCount = countByCondition(petName, serviceType, status, appointmentDate, ownerId);

        // 添加排序和分页
        sql.append(" ORDER BY a.appointment_date DESC, a.appointment_time DESC LIMIT ?, ?");
        params.add((currentPage - 1) * pageSize);
        params.add(pageSize);

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return new PageResult<>(appointments, currentPage, pageSize, totalCount);
    }

    @Override
    public long countAll() {
        String sql = "SELECT COUNT(*) FROM appointments";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM appointments WHERE owner_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByStatus(String status) {
        String sql = "SELECT COUNT(*) FROM appointments WHERE status = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByDate(Date appointmentDate) {
        String sql = "SELECT COUNT(*) FROM appointments WHERE appointment_date = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, appointmentDate);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countTodayAppointments() {
        String sql = "SELECT COUNT(*) FROM appointments WHERE DATE(appointment_date) = CURDATE()";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countTodayAppointmentsByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM appointments WHERE owner_id = ? AND DATE(appointment_date) = CURDATE()";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countPendingAppointmentsByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM appointments WHERE owner_id = ? AND status = 'pending'";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public int updateStatus(Integer appointmentId, String status) {
        String sql = "UPDATE appointments SET status = ? WHERE appointment_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status);
            pstmt.setInt(2, appointmentId);

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }

        return 0;
    }

    @Override
    public int assignDoctor(Integer appointmentId, String doctorName) {
        String sql = "UPDATE appointments SET doctor_name = ? WHERE appointment_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, doctorName);
            pstmt.setInt(2, appointmentId);

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }

        return 0;
    }

    @Override
    public List<Object[]> getStatusStatistics() {
        String sql = "SELECT status, COUNT(*) as count FROM appointments GROUP BY status ORDER BY count DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] stat = new Object[2];
                stat[0] = rs.getString("status");
                stat[1] = rs.getLong("count");
                statistics.add(stat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    @Override
    public List<Object[]> getServiceTypeStatistics() {
        String sql = "SELECT service_type, COUNT(*) as count FROM appointments GROUP BY service_type ORDER BY count DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] stat = new Object[2];
                stat[0] = rs.getString("service_type");
                stat[1] = rs.getLong("count");
                statistics.add(stat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    @Override
    public List<Appointment> findTodayAppointments() {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.appointment_date = CURDATE() " +
                    "ORDER BY a.appointment_time ASC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return appointments;
    }

    @Override
    public List<Appointment> findPendingAppointments() {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.status = 'pending' " +
                    "ORDER BY a.appointment_date ASC, a.appointment_time ASC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return appointments;
    }

    @Override
    public List<Appointment> findUpcomingAppointments() {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.appointment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) " +
                    "AND a.status IN ('pending', 'confirmed') " +
                    "ORDER BY a.appointment_date ASC, a.appointment_time ASC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return appointments;
    }

    @Override
    public List<Appointment> findRecentByOwnerId(Integer ownerId, int limit) {
        String sql = "SELECT a.*, p.pet_name, p.pet_type, u.real_name as owner_name " +
                    "FROM appointments a " +
                    "LEFT JOIN pets p ON a.pet_id = p.pet_id " +
                    "LEFT JOIN users u ON a.owner_id = u.user_id " +
                    "WHERE a.owner_id = ? " +
                    "ORDER BY a.create_time DESC LIMIT ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Appointment> appointments = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            pstmt.setInt(2, limit);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                appointments.add(mapResultSetToAppointment(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return appointments;
    }

    @Override
    public boolean checkTimeConflict(Date appointmentDate, Time appointmentTime, Integer excludeId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM appointments WHERE appointment_date = ? AND appointment_time = ?");
        if (excludeId != null) {
            sql.append(" AND appointment_id != ?");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setDate(1, appointmentDate);
            pstmt.setTime(2, appointmentTime);
            if (excludeId != null) {
                pstmt.setInt(3, excludeId);
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return false;
    }

    /**
     * 根据条件统计预约数量
     *
     * @param petName 宠物姓名
     * @param serviceType 服务类型
     * @param status 预约状态
     * @param appointmentDate 预约日期
     * @param ownerId 主人ID
     * @return long 预约数量
     */
    private long countByCondition(String petName, String serviceType, String status,
                                Date appointmentDate, Integer ownerId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM appointments a " +
                                            "LEFT JOIN pets p ON a.pet_id = p.pet_id WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (petName != null && !petName.trim().isEmpty()) {
            sql.append(" AND p.pet_name LIKE ?");
            params.add("%" + petName.trim() + "%");
        }
        if (serviceType != null && !serviceType.trim().isEmpty()) {
            sql.append(" AND a.service_type = ?");
            params.add(serviceType);
        }
        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND a.status = ?");
            params.add(status);
        }
        if (appointmentDate != null) {
            sql.append(" AND a.appointment_date = ?");
            params.add(appointmentDate);
        }
        if (ownerId != null) {
            sql.append(" AND a.owner_id = ?");
            params.add(ownerId);
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }
}
